#! /bin/bash 
source /etc/profile 
# 可以输入日期；如果未输入日期取昨天的时间 
if [ -n "$1" ] 
then
do_date=$1 
else
do_date=`date -d "-1 day" +%F` 
fi

# 定义要执行的SQL 
sql="
insert overwrite table dim.dim_trade_shops
-- 新增的数据
SELECT
  shopid,
  shopname,
  shoplevel,
  STATUS,
  createtime,
  modifytime,
  CASE WHEN modifytime IS NOT NULL THEN substr(modifytime, 0, 10)
  ELSE substr(createtime, 0, 10) END AS start_dt,
  '9999-12-31' AS end_dt 
FROM ods.ods_trade_shops WHERE dt='$do_date'
union all
-- 历史数据，未变化的不用处理；变化的数据将结束日期-1
SELECT
  B.shopid,
  B.shopname,
  B.shoplevel,
  B.STATUS,
  B.createtime,
  B.modifytime,
  B.start_dt,
  CASE WHEN A.shopid IS NOT NULL THEN date_add('$do_date', -1) ELSE B.end_dt END AS end_dt 
FROM (select * from ods.ods_trade_shops where dt='$do_date') A
right join dim.dim_trade_shops B ON A.shopid=B.shopid;
"

hive -e "$sql"
